﻿using System;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Nvigorate.Common;
using Nvigorate.Data;
using Nvigorate.Data.Query;
using Nvigorate.Relational.Mapping.Index;

namespace Nvigorate.Test.TSQL
{
    /// <summary>
    /// Summary description for SqlGen
    /// </summary>
    [TestClass]
    public class SqlGen
    {
        #region Shit No One Cares About
        public SqlGen()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        private TestContext testContextInstance;

        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }

        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        // Use TestInitialize to run code before running each test 
        // [TestInitialize()]
        // public void MyTestInitialize() { }
        //
        // Use TestCleanup to run code after each test has run
        // [TestCleanup()]
        // public void MyTestCleanup() { }
        //
        #endregion

#endregion
        
        [TestMethod]
        public void Test_DefineSelect()
        {
            var selectQuery = new Select
            (
                    Table.From("table1").As("T1")
                        .Join
                        (
                            Table.InnerJoin("table2").As("T2").On(Criterion.Column("Column1", "T1").EqualTo((Reference)"T2.Column2")),
                            Table.InnerJoin("table3").As("T3").On(Criterion.Column("Column1", "T1").EqualTo((Reference)"T3.Column2"))
                        ),
                    Select.Column("Column1").OwnedBy("table1").As("Alias1") +
                    Select.Column("Column2").OwnedBy("table1").As("Alias2") +
                    Select.Column("Column3").OwnedBy("table1").As("Alias3") +
                    Select.Column("Column1").OwnedBy("table2").As("Alias4"),
                    Where.Criteria(Criterion.Column("Column1", "table1").EqualTo((Literal) "1")),
                    Page.PageSize(10).PageIndex(1),
                    Order.By().Ascending("Column1").Descending("Column2")
            );

            var parameterizer = new Parameterizer();
            var provider = new MSSQL9Provider();
            var index = new MapIndex();
            var paramList = parameterizer.Parameterize(selectQuery, provider, index.GetRepositoryMapIndex(""));

            var visitor = new MSSQL9Visitor();
            var query = visitor.Translate(selectQuery);


            DelimitedBuilder builder = new DelimitedBuilder(" ");
            builder.Append("SELECT");
            selectQuery
                .ColumnList
                .OfType<Column>()
                .ToList()
                .ForEach(e => builder.AppendFormat("[{0}.{1}] AS [{2}]", e.Owner, e.Name, e.Alias ));
            builder.AppendFormat("FROM {0} {1}", selectQuery.Table.Name, selectQuery.Table.Alias);
            GetJoins(builder, selectQuery.Table);
            selectQuery.Qualifiers.ForEach(builder.Append);
        }

        public void GetJoins(DelimitedBuilder builder, Table table)
        {
            table.JoinedTables
                .ToList()
                .ForEach(j =>
                {
                    builder.AppendFormat("JOIN {0} {1} ON {2}", j.Name, j.Alias, j.JoinCriteria);
                    GetJoins(builder, j);
                });
        }

        [TestMethod]
        public void Test_DefineInsert()
        {
            var insertQuery1 =
                Insert.Query
                    (
                        new Table("table1"),
                        Insert.Column("column1") + 
                        Insert.Column("column1") +
                        Insert.Column("column3"),
                        (Literal) 10 +
                        QueryValue.Literal("B") +
                        QueryValue.Literal("B")
                    );

            var insertQuery2 =
                Insert.Query
                (
                    new Table("table1"),
                    QueryValue.Literal("A") +
                    QueryValue.Literal("B") +
                    QueryValue.Literal("B")
                );

            var insertQuery3 =
                Insert.Query
                    (
                        new Table("table1"),
                        Insert.Column("column1") +
                        Insert.Column("column1") +
                        Insert.Column("column3"),
                        Select.Query
                        (
                            Table.From("table2"),
                            Select.Column("column1") +
                            Select.Column("column2") +
                            Select.Column("column3") 
                        )
                    );
        }

        [TestMethod]
        public void Test_DefineUpdate()
        {
            var updateQuery = new Update
                (
                    Table.From("table1"),
                    Update.Column("column1").Set(QueryValue.Literal("CapitanoAmurrca")) +
                    Update.Column("column2").Set(QueryValue.Literal("Terpadurp!")),
                    Where.Criteria(Criterion.Column("column3", "table1").EqualTo((Literal)"poo"))
                );
        }

        [TestMethod]
        public void Test_DefineDelete()
        {
            
        }
    
        [TestMethod]
        public void Test_DefineTableVariable()
        {
            var test = (Literal) DBNull.Value;

        }

        [TestMethod]
        public void Test_Criteria()
        {
            SearchCriteria criteria = 
                            (Criterion.Column("test1", "table1").EqualTo((Literal) 10) &
                            Criterion.Column("test2", "table1").Like((Literal)"%poo%")) |
                            (Criterion.Column("test1", "table2").GreaterOrEqualThan((Literal) 50) | 
                            Criterion.Column("test2", "table2").Between((Literal) DateTime.Now.Subtract(new TimeSpan(1, 0, 0, 0))).And((Literal) DateTime.Now));

            var parameterizer = new Parameterizer();
            var provider = new MSSQL9Provider();
            var index = new MapIndex();
            var paramList = parameterizer.Parameterize(criteria, provider, index.GetRepositoryMapIndex(""));

            var visitor = new MSSQL9Visitor();
            var query = visitor.Translate(criteria);

            Assert.AreEqual(paramList.Count, 5);

            Assert.IsTrue(criteria[0].Criteria.Count == 2);
        }


        [TestMethod]
        public void Test_SelectFromTableVariable()
        {
            var batch =
                new TableVariable
                    (
                        Table.Variable("Employees"),
                        TableVariable.Column("EmployId").OwnedBy("Employees").Type(typeof(string)) +
                        TableVariable.Column("ManagerId").OwnedBy("Employees").Type(typeof(string)) +
                        TableVariable.Column("CustomerId").OwnedBy("Employees").Type(typeof(string)) +
                        TableVariable.Column("RoleId").OwnedBy("Employees").Type(typeof(int)) +
                        TableVariable.Column("CompletedEnrollment").OwnedBy("Employees").Type(typeof(bool))
                    ) +
                Insert.Query
                    (
                        new Table("@Employees"),
                        Insert.Column("EmployId").OwnedBy("Employees") +
                        Insert.Column("ManagerId").OwnedBy("Employees") +
                        Insert.Column("CustomerId").OwnedBy("Employees") +
                        Insert.Column("RoleId").OwnedBy("Employees") +
                        Insert.Column("CompletedEnrollment").OwnedBy("Employees"),
                        Select.Query
                        (
                            Table.From("Employees"),
                            Select.Column("EmployId").OwnedBy("Employees") +
                            Select.Column("ManagerId").OwnedBy("Employees") +
                            Select.Column("CustomerId").OwnedBy("Employees") +
                            Select.Column("RoleId").OwnedBy("Employees") +
                            Select.Column("CompletedEnrollment").OwnedBy("Employees"),
                            Where.Criteria(
                                Criterion.Column("ManagerId", "Employees").EqualTo((Literal) DBNull.Value) &
                                Criterion.Column("RoleId", "Employees").EqualTo((Literal) 3)),
                            Order.By().Ascending("EmployId", "Employees"),
                            Page.PageSize(10).PageIndex(2)
                        )
                    ) +
                Select.Query
                    (
                        Table.From("@Employees"),
                        Select.Column("*")
                    );

            var provider = new MSSQL9Provider();
            var index = new MapIndex();

            var paramList = batch.Parameterize(provider, index.GetRepositoryMapIndex(""));
            var query = batch.Translate(provider);

            Assert.IsInstanceOfType(batch, typeof(QueryList));
        }
    }


    /*
     
     declare table @tableName1
     (
        [tableName1.column1] type(specification),
        [tableName1.column2] type(specification),   
        [tableName2.column1] type(specification),   
        [tableName2.column2] type(specification),   
     )
     
     INSERT INTO @tableName1
        SELECT 
            tableName1.column1 AS tablename1.column1,
            tablename1.column2 AS tablename1.column2,
            tablename2.column1 AS tablename2.column1,
            tablename2.column2 AS tablename2.column2
        FROM tableName1
        JOIN tableName2 ON
            tableName2.column2 = tableName1.column1
        WHERE
            tableName1.column1 = parameter
        ORDER BY
        GROUP BY
     
     declare table @tableName2
     (
        [tableName3.column1] type(specification),
        [tableName3.column2] type(specification),
        [tableName3.column3] type(specification)
     )
     
     INSERT INTO @tableName2
        SELECT
            tableName3.column1 AS tableName3.column1,
            tableName3.column2 AS tableName3.column2,
            tableName3.column3 AS tableName3.column3,
        FROM tableName3
        WHERE
            tableName3.column3 in (SELECT table1.column1 FROM @tableName1)
       
     SELECT * FROM @tableName1
     SELECT * FROM @tableName2
     
     */
}
